package com.koron.css2.common.utils;

import com.koron.util.Constant;
import com.koron.util.FileUtils;
import com.koron.util.Tools;
import org.apache.commons.io.FilenameUtils;
import org.apache.commons.io.IOUtils;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.HttpEntity;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.swan.bean.MessageBean;
import org.swan.excel.Excel;

import java.io.*;
import java.math.BigDecimal;
import java.net.URL;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtils {

	private static Logger log = LogManager.getLogger(ExcelUtils.class);

	private static final String XLS = "xls";

	private static final String XLSX = "xlsx";

	public static final int BATCH_COUNT = 40000;

	/**
	 * @作者 yan
	 * @创建日期
	 * @版本 V1.0
	 * @描述 Excel 导出通用工具类
	 */
	public static byte[] writeExcel(String sheetTitle, String[] rowTitle, List<Object[]> data) {
		Workbook workBook = new XSSFWorkbook ();//创建excel表
		Sheet sheet = workBook.createSheet(sheetTitle);
		sheet.setDefaultColumnWidth(20);//设置默认行宽

		//表头样式（加粗，水平居中，垂直居中）
		CellStyle cellStyle = workBook.createCellStyle();
		cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
		cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

		//设置边框样式
		cellStyle.setBorderBottom(BorderStyle.THIN); //下边框
		cellStyle.setBorderLeft(BorderStyle.THIN);//左边框
		cellStyle.setBorderTop(BorderStyle.THIN);//上边框
		cellStyle.setBorderRight(BorderStyle.THIN);//右边框

		Font fontStyle = workBook.createFont();
		fontStyle.setBold(true);

		cellStyle.setFont(fontStyle);

		//标题样式（加粗，垂直居中）
		CellStyle titleStyle = workBook.createCellStyle();
		titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
		titleStyle.setFont(fontStyle);

		//设置边框样式
		titleStyle.setBorderBottom(BorderStyle.THIN); //下边框
		titleStyle.setBorderLeft(BorderStyle.THIN);//左边框
		titleStyle.setBorderTop(BorderStyle.THIN);//上边框
		titleStyle.setBorderRight(BorderStyle.THIN);//右边框

		//字段样式（垂直居中）
		CellStyle fieldStyle = workBook.createCellStyle();
		fieldStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中

		//设置边框样式
		fieldStyle.setBorderBottom(BorderStyle.THIN); //下边框
		fieldStyle.setBorderLeft(BorderStyle.THIN);//左边框
		fieldStyle.setBorderTop(BorderStyle.THIN);//上边框
		fieldStyle.setBorderRight(BorderStyle.THIN);//右边框
		//创建表头
		Row row = sheet.createRow(0);
		row.setHeightInPoints(20);//行高
		Cell cell = row.createCell(0);
		cell.setCellValue(sheetTitle);
		cell.setCellStyle(cellStyle);
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (rowTitle.length - 1)));
		//创建标题
		Row hssfRow = sheet.createRow(1);
		hssfRow.setHeightInPoints(20);
		Cell hc;
		for (int i = 0; i < rowTitle.length; i++) {
			hc = hssfRow.createCell(i);
			hc.setCellValue(rowTitle[i]);
			hc.setCellStyle(titleStyle);
		}
		ByteArrayOutputStream out = null;
		try {
			int i = 2;
			for (Object[] rows : data) {
				Row rowBody = sheet.createRow(i);
				rowBody.setHeightInPoints(20);
				int j = 0;
				for (Object str : rows) {
					hc = rowBody.createCell(j);
					hc.setCellValue(Objects.toString(str, ""));
					hc.setCellStyle(fieldStyle);
					j++;
				}
				i++;
			}
			out = new ByteArrayOutputStream();
			workBook.write(out);
			return out.toByteArray();
		} catch (Exception ex) {
			throw new RuntimeException("EXCEL 写入数据失败，原因：" + ex.getMessage(), ex);
		} finally{
			try {
				if (null != workBook) {
					workBook.close();
				}
				if (null != out) {
					out.close();
				}
			} catch (Exception e) {

			}
		}
	}

	public static String[] readExcelHeader(byte[] buf, String filename) {
		Workbook workbook = null;
		InputStream inputStream = null;
		try {
			String fileType = FilenameUtils.getExtension(filename);
			inputStream = new ByteArrayInputStream(buf);
			workbook = getWorkbook(inputStream, fileType);
			return parseExcelHeader(workbook);
		} catch (Exception e) {
			log.error("解析Excel失败，文件名：" + filename + " 错误信息：" + e.getMessage());
			throw new RuntimeException(e);
		} finally {
			try {
				if (null != workbook) {
					workbook.close();
				}
				if (null != inputStream) {
					inputStream.close();
				}
			} catch (Exception e) {

			}
		}
	}

	/**
	 * 读取Excel文件内容
	 *
	 * @param file
	 *            要读取的Excel文件所在路径
	 * @return 读取结果列表，读取失败时返回null
	 */
	public static List<String[]> readExcelData(File file, boolean header) {
		Workbook workbook = null;
		InputStream inputStream = null;
		try {
			String fileType = FilenameUtils.getExtension(file.getName());
			inputStream = new FileInputStream(file);
			workbook = getWorkbook(inputStream, fileType);
			return parseExcel(workbook, header);
		} catch (Exception e) {
			log.error("解析Excel失败，文件名：" + file.getName() + " 错误信息：" + e.getMessage());
			throw new RuntimeException(e);
		} finally {
			try {
				if (null != workbook) {
					workbook.close();
				}
				if (null != inputStream) {
					inputStream.close();
				}
			} catch (Exception e) {

			}
		}
	}

	/**
	 * 读取Excel文件内容
	 *
	 * @param filename
	 *            要读取的Excel文件所在路径
	 * @return 读取结果列表，读取失败时返回null
	 */
	public static List<String[]> readExcelData(byte[] buf, String filename, boolean header) {
		Workbook workbook = null;
		InputStream inputStream = null;
		try {
			String fileType = FilenameUtils.getExtension(filename);
			inputStream = new ByteArrayInputStream(buf);
			workbook = getWorkbook(inputStream, fileType);
			return parseExcel(workbook, header);
		} catch (Exception e) {
			log.error("解析Excel失败，文件名：" + filename + " 错误信息：" + e.getMessage());
			throw new RuntimeException(e);
		} finally {
			try {
				if (null != workbook) {
					workbook.close();
				}
				if (null != inputStream) {
					inputStream.close();
				}
			} catch (Exception e) {

			}
		}
	}

	public static HttpEntity<?> exportExcel(List<?> data, String fileName, String fileTemplate, String btlTemplate){
		//TODO 这里写死只能导出小于等于40000条记录
		if(data.size() > 40000){
			throw new RuntimeException("导出的数据不能超过4万条");
		}
		fileTemplate = "template/excel/" + fileTemplate;
		URL url = ExcelUtils.class.getClassLoader().getResource(fileTemplate);
		if(url == null) {
			log.error("类路径下不存在{}文件", fileTemplate);
			throw new NullPointerException("类路径下不存在" + fileTemplate + "文件");
		}
		Map<String, Object> map = new HashMap<String, Object>();
		map.put("data", data);
		return Excel.createFile(fileName, new File(url.getPath()), btlTemplate, map);
	}

	public static HttpEntity<?> exportExcelZip(List<?> data, String fileName, File excelFile, String btlTemplate) {
		File zipFolder = FileUtils.templateZipFolder(Tools.getObjectId());
		List<File> files = new ArrayList<File>();
		int iCount = data.size();
		int size = iCount / BATCH_COUNT;
		int remainder = iCount % BATCH_COUNT; // 余数
		if(remainder > 0) {
			size = size + 1;
		}
		try {
			long begin = System.currentTimeMillis();
			Map<String, Object> map = new HashMap<String, Object>();
			for(int i = 1; i <= size; i++) {
				int beginIndex = (i - 1) * BATCH_COUNT;
				int endIndex = i * BATCH_COUNT;
				if (endIndex > data.size()){
					endIndex = data.size();
				}
				map.put("data", data.subList(beginIndex, endIndex));
				files.add(templateExcelFile("report(" + i + ")", zipFolder, excelFile, btlTemplate, map));
			}
			byte[] bytes;
			File zipFile = FileUtils.templateZipFile(zipFolder.getName() + ".zip");
			zipFile = FileUtils.toZip(files, zipFile);
			try(InputStream input = new FileInputStream(zipFile)) {
				bytes = IOUtils.toByteArray(input);
			}
			log.info("共花费：{} 秒", ((System.currentTimeMillis() - begin) / 1000));
			HttpHeaders header = new HttpHeaders();
			header.add("Content-Disposition", "attachment ; filename=" + new String(fileName.getBytes("utf8"),"ISO8859-1")+".zip");
			header.add("Content-Type", "application/octet-stream");
			return new ResponseEntity<byte[]>(bytes, header, HttpStatus.CREATED);
		}catch (Exception ex){
			log.error("EXCEL 导出ZIP 失败：" + ex);
		}
		return new ResponseEntity<String>("EXCEL导出失败", HttpStatus.OK);
	}

	/**
	 * 根据文件后缀名类型获取对应的工作簿对象
	 *
	 * @param inputStream
	 *            读取文件的输入流
	 * @param fileType
	 *            文件后缀名类型（xls或xlsx）
	 * @return 包含文件数据的工作簿对象
	 * @throws IOException
	 */
	private static Workbook getWorkbook(InputStream inputStream, String fileType) throws IOException {
		Workbook workbook = null;
		if (fileType.equalsIgnoreCase(XLS)) {
			workbook = new HSSFWorkbook(inputStream);
		} else if (fileType.equalsIgnoreCase(XLSX)) {
			workbook = new XSSFWorkbook(inputStream);
		}
		return workbook;
	}

	/**
	 * 解析Excel数据
	 *
	 * @param workbook
	 *            Excel工作簿对象
	 * @return 解析结果
	 */
	private static List<String[]> parseExcel(Workbook workbook, boolean header) {
		List<String[]> result = new ArrayList<String[]>();
		Sheet sheet = workbook.getSheetAt(0); //默认取第一个sheet
		if (sheet == null) {
			return result;
		}
		int firstRowNum = sheet.getFirstRowNum();
		Row firstRow = sheet.getRow(firstRowNum);
		if (null == firstRow) {
			log.info("解析Excel失败, 在第一行没有读取到任何数据！");
			return result;
		}
		int rowStart = firstRowNum;
		if(header) {
			rowStart += 1;
		}
		int rowEnd = sheet.getPhysicalNumberOfRows();
		for (int rowNum = rowStart; rowNum < rowEnd; rowNum++) {
			Row row = sheet.getRow(rowNum);
			if (null == row) {
				continue;
			}
			int cells = row.getLastCellNum();
			if (0 == cells) {
				continue;
			}
			String[] arr = convertRowToData(row);
			if(Arrays.stream(arr).allMatch(x-> x==""||x==null
			)){
				continue;
			}
			result.add(arr);
		}
		return result;
	}

	private static String[] parseExcelHeader(Workbook workbook) {
		Sheet sheet = workbook.getSheetAt(0);
		int firstRowNum = sheet.getFirstRowNum();
		Row firstRow = sheet.getRow(firstRowNum);
		return convertRowToData(firstRow);
	}

	/**
	 * 将单元格内容转换为字符串
	 *
	 * @param cell
	 * @return
	 */
	private static String convertCellValueToString(Cell cell) {
		if (cell == null) {
			return null;
		}
		String value = null;
		switch (cell.getCellTypeEnum()) {
			case NUMERIC:{ // 数字和日期
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					value = new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue());
					break;
				}
				boolean b = true;//true为整型
				String str = String.valueOf(cell.getNumericCellValue());
				str = new BigDecimal(str+"").toPlainString();
				if (str.indexOf(".") > 0) {
					String tempStr = str.substring(str.indexOf(".")+1);
					char[] tempArray = tempStr.toCharArray();
					for (int i = 0; i < tempArray.length; i++) {
						char s = tempArray[i];
						if (!String.valueOf(s).equals("0")) {
							b = false;
							break;
						}
					}
					if (b) {
						//整型
						str = str.substring(0, str.indexOf("."));
						value = str;
					} else {
						//浮点型
						value = String.valueOf(cell.getNumericCellValue());
					}
				} else {
					//整型
					value = str;
				}
				break;
			}
			case STRING:{ // 字符串
				value = cell.getStringCellValue();
				break;
			}
			case BOOLEAN:{ // 布尔
				Boolean booleanValue = cell.getBooleanCellValue();
				value = booleanValue.toString();
				break;
			}
			case BLANK:{ // 空值
				value = "";
				break;
			}
			case FORMULA:{ // 公式
				value = cell.getCellFormula();
				value = "";
				break;
			}
			case ERROR:{ // 故障
				value = "";
				break;
			}
			default:
				break;
		}
		return value;
	}

	/**
	 * 提取每一行中需要的数据，构造成为一个结果数据对象
	 *
	 * 当该行中有单元格的数据为空或不合法时，忽略该行的数据
	 *
	 * @param row
	 *            行数据
	 * @return 解析后的行数据对象，行数据错误时返回null
	 */
	private static String[] convertRowToData(Row row) {
		int firstCellNum = row.getFirstCellNum();
		int cellLength = row.getLastCellNum();
		String[] data = new String[cellLength];
		for(int i = firstCellNum; i < cellLength; i++) {
			Cell cell = row.getCell(i);
			data[i] = convertCellValueToString(cell);
		}
		return data;
	}

	private static File templateExcelFile(String fileName, File zipFolder, File exceFile, String template, Map<String, Object> map) {
		MessageBean<byte[]> message = Excel.createFile(exceFile, template, map);
		if(Constant.MESSAGE_INT_SUCCESS != message.getCode()){
			throw new RuntimeException("excel 写入数据失败：" + message.getDescription());
		}
		String filepath = zipFolder.getPath() + File.separator + fileName + ".xlsx";
		File excelFile = new File(filepath);
		try {
			if(!excelFile.exists()) {
				excelFile.createNewFile();
			}
			try(OutputStream output = new FileOutputStream(excelFile)){
				IOUtils.write(message.getData(), output);
			}
		}catch (Exception ex){
			throw new RuntimeException("excel 写入临时文件失败", ex);
		}
		return excelFile;
	}

	/**
	 * 隐藏Sheet方式创建下拉框(单元格下拉框数据大于255字节时使用)
	 *
	 * @param sheet  需要添加下拉框的Sheet
	 * @param firstRow 起始行
	 * @param firstCol 其实列
	 * @param endRow   终止行
	 * @param endCol   终止列
	 * @param dataArray  下拉框数组
	 * @param wbCreat    所在excel的WorkBook，用于创建隐藏Sheet
	 * @param hidddenSheetName    隐藏Sheet的名称
	 * @return
	 */
	public void createDropDownListWithHiddenSheet(Sheet sheet, int firstRow,
												  int firstCol, int endRow,
												  int endCol, String[] dataArray,
												  Workbook wbCreat,
												  String hidddenSheetName) {

		Sheet hidden = wbCreat.createSheet(hidddenSheetName);
		Cell cell = null;
		for (int i = 0, length = dataArray.length; i < length; i++) {
			String name = dataArray[i];
			Row row = hidden.createRow(i);
			cell = row.createCell(0);
			cell.setCellValue(name);
		}
		Name namedCell = wbCreat.createName();
		namedCell.setNameName(hidddenSheetName);
		namedCell.setRefersToFormula(hidddenSheetName + "!$A$1:$A$" + dataArray.length);
		wbCreat.setSheetHidden(wbCreat.getSheetIndex(hidden), true);
		DataValidationConstraint constraint = null;
		CellRangeAddressList addressList = new CellRangeAddressList(firstRow, endRow, firstCol,
				endCol);
		// 创建 DataValidation
		DataValidation validation = null;
		if (sheet instanceof XSSFSheet || sheet instanceof SXSSFSheet) {
			DataValidationHelper dvHelper = sheet.getDataValidationHelper();
			constraint = dvHelper.createFormulaListConstraint(hidddenSheetName);
			validation = dvHelper.createValidation(constraint, addressList);
		} else {
			constraint = DVConstraint.createFormulaListConstraint(hidddenSheetName);
			validation = new HSSFDataValidation(addressList, constraint);
		}
		if (validation instanceof HSSFDataValidation) {
			validation .setSuppressDropDownArrow(false);
		} else {
			validation .setSuppressDropDownArrow(true);
			validation .setShowErrorBox(true);
		}
		sheet.addValidationData(validation);
	}

}
